﻿using System;
using System.Collections.Generic;
using System.Linq;
using System.Web;
using System.Web.UI;
using System.Web.UI.WebControls;
using System.Configuration;
using System.Data.SqlClient;

namespace FYPHP.Reports
{
    public partial class VirtualShopReport : System.Web.UI.Page
    {
        SqlConnection conn = new SqlConnection(ConfigurationManager.ConnectionStrings["fyphp"].ConnectionString);

        protected void Page_Load(object sender, EventArgs e)
        {
            if (!IsPostBack)
            {
                DropDownList1.Items.Add("Date");
                DropDownList1.Items.Add("Total");

                SqlCommand cmdReport;
                SqlDataReader dtrReport;
                conn.Open();

                cmdReport = new SqlCommand("SELECT COUNT(tt.t_id) AS ttrans, SUM(tt.t_total) AS ttotal, CONVERT(varchar, tt.t_date, 103) AS tdate,uu.username,vs.v_id FROM Transactions tt,Users uu,Virtual_Shop vs WHERE tt.t_seller=uu.u_id AND tt.t_seller=vs.u_id AND (tt.t_status='CartCompleted' OR tt.t_status='BuyNowCompleted' OR tt.t_status='CartShipped' OR tt.t_status='BuyNowShipped') AND uu.u_position='ShopOwner' GROUP BY CONVERT(varchar, tt.t_date, 103), tt.t_seller,uu.username, vs.v_id", conn);
                dtrReport = cmdReport.ExecuteReader();

                ItemList.DataSource = dtrReport;
                ItemList.DataBind();

                dtrReport.Close();
                cmdReport = new SqlCommand("SELECT SUM(t.t_total) FROM Transactions t,Users u WHERE u.u_id=t.t_seller AND (t.t_status='CartCompleted' OR t.t_status='BuyNowCompleted' OR t.t_status='CartShipped' OR t.t_status='BuyNowShipped') AND u.u_position='ShopOwner' ", conn);
                double total = Convert.ToDouble(cmdReport.ExecuteScalar());

                lblTotal.Text = total.ToString();
                conn.Close();

                MultiView1.ActiveViewIndex = 0;
                Panel1.Visible = true;
                Panel2.Visible = false;
            }
        }

        protected void RadioButtonList1_SelectedIndexChanged(object sender, EventArgs e)
        {
            SqlCommand cmdReport;
            SqlDataReader dtrReport;
            conn.Open();

            cmdReport = new SqlCommand("SELECT SUM(t.t_total) FROM Transactions t,Users u WHERE u.u_id=t.t_seller AND (t.t_status='CartCompleted' OR t.t_status='BuyNowCompleted' OR t.t_status='CartShipped' OR t.t_status='BuyNowShipped') AND u.u_position='ShopOwner'", conn);
            double total = Convert.ToDouble(cmdReport.ExecuteScalar());


            if (RadioButtonList1.SelectedValue == "Daily")
            {
                TextBox1.Text = "";

                TextBox3.Text = "";
                DropDownList1.Items.Clear();
                DropDownList1.Items.Add("Date");
                DropDownList1.Items.Add("Total");

                //CONVERT(varchar, t_date, 103) - dd/mm/yy
                //Obtained the Transaction ID, TOtal , Date of Transaction , Buyername , Seller Name

                cmdReport = new SqlCommand("SELECT COUNT(tt.t_id) AS ttrans, SUM(tt.t_total) AS ttotal, CONVERT(varchar, tt.t_date, 103) AS tdate, uu.username,vs.v_id FROM Transactions tt,Users uu WHERE tt.t_seller=uu.u_id AND tt.t_seller=vs.u_id AND (tt.t_status='CartCompleted' OR tt.t_status='BuyNowCompleted' OR tt.t_status='CartShipped' OR tt.t_status='BuyNowShipped') AND uu.u_position='ShopOwner' GROUP BY CONVERT(varchar, tt.t_date, 103), tt.t_seller,uu.username,vs.v_id", conn);
                dtrReport = cmdReport.ExecuteReader();

                ItemList.DataSource = dtrReport;
                ItemList.DataBind();

                dtrReport.Close();
                lblTotal.Text = total.ToString();
                MultiView1.ActiveViewIndex = 0;

                Panel1.Visible = true;
                Panel2.Visible = false;

            }
            else if (RadioButtonList1.SelectedValue == "Monthly")
            {
                cmdReport = new SqlCommand("SELECT DISTINCT(DATEPART(year, t_date)) AS year FROM Transactions", conn);
                dtrReport = cmdReport.ExecuteReader();
                ddlMonth.DataSource = dtrReport;
                ddlMonth.DataTextField = "year";
                ddlMonth.DataBind();
                dtrReport.Close();
                ddlMonth.Items.Insert(0, new ListItem("-------", String.Empty));
                ddlMonth.SelectedIndex = 0;


                TextBox1.Text = "";
                TextBox3.Text = "";
                DropDownList1.Items.Clear();
                DropDownList1.Items.Add("Month");
                DropDownList1.Items.Add("Transactions");
                DropDownList1.Items.Add("Total");

                //SELECT DATEPART(datepart,'2007-10-30 12:15:32.1234567 +05:10'). - 2007 if DATEPART(Year,date)  & DATENAME(month,date)  - October

                //Obtained Year,Month,Counter of Transaction and Total of Transaction

                cmdReport = new SqlCommand("SELECT DATEPART(year,t.t_date) AS tyear,  DATENAME(month,t.t_date) AS tmonth, COUNT(t.t_id) AS ttrans, SUM(t.t_total) AS ttotal,u.username FROM Users u,Transactions t WHERE u.u_id=t.t_seller AND (t.t_status='CartCompleted' OR t.t_status='BuyNowCompleted' OR t.t_status='CartShipped' OR t.t_status='BuyNowShipped') AND u.u_position='ShopOwner' GROUP BY DATEPART(year, t.t_date), DATENAME(month,t.t_date), t.t_seller,u.username", conn);
                dtrReport = cmdReport.ExecuteReader();

                ItemList2.DataSource = dtrReport;
                ItemList2.DataBind();
                dtrReport.Close();

                lblTotal2.Text = total.ToString();
                MultiView1.ActiveViewIndex = 1;
                Panel1.Visible = false;
                Panel2.Visible = true;
            }
            else if (RadioButtonList1.SelectedValue == "Yearly")
            {
                DropDownList1.Items.Clear();
                DropDownList1.Items.Add("Year");
                DropDownList1.Items.Add("Transactions");
                DropDownList1.Items.Add("Total");

                //SELECT DATEPART(datepart,'2007-10-30 12:15:32.1234567 +05:10'). - 2007 if DATEPART(Year,date)  & DATENAME(month,date)  - October

                //Obtained Year Counter of Transaction and Total of Transaction

                cmdReport = new SqlCommand("SELECT DATEPART(year, t.t_date) AS tyear, COUNT(t.t_id) AS ttrans, SUM(t.t_total) AS ttotal,u.username FROM Transactions t, Users u WHERE u.u_id=t.t_seller AND (t.t_status='CartCompleted' OR t.t_status='BuyNowCompleted' OR t.t_status='CartShipped' OR t.t_status='BuyNowShipped') AND u.u_position='ShopOwner' GROUP BY DATEPART(year, t.t_date), t.t_seller,u.username", conn);
                dtrReport = cmdReport.ExecuteReader();

                ItemList3.DataSource = dtrReport;
                ItemList3.DataBind();
                dtrReport.Close();

                lblTotal3.Text = total.ToString();
                MultiView1.ActiveViewIndex = 2;
                Panel1.Visible = false;
                Panel2.Visible = false;
            }

            conn.Close();
        }


        protected void btnSort_Click(object sender, EventArgs e)
        {
            SqlCommand cmdReport, cmdReport2;
            SqlDataReader dtrReport;
            conn.Open();

            String sort = "", sc = "";

            if (CheckBox1.Checked)
                sc = "DESC";

            if (RadioButtonList1.SelectedValue == "Daily")
            {
                if (DropDownList1.SelectedValue == "Date")
                    sort = "CONVERT(varchar, tt.t_date, 103)";
                else if (DropDownList1.SelectedValue == "Total")
                    sort = "ttotal";

                //If From is not Empty and To is not Empty

                if (TextBox1.Text != "" && TextBox3.Text != "")
                {
                    //Obtained the Transaction ID, TOtal , Date of Transaction , Buyername , Seller Name

                    cmdReport = new SqlCommand("SELECT COUNT(tt.t_id) AS ttrans, SUM(tt.t_total) AS ttotal, CONVERT(varchar, tt.t_date, 103) AS tdate,uu.username,vs.v_id FROM Transactions tt,Users uu WHERE tt.t_seller=uu.u_id AND tt.t_seller=vs.u_id AND (tt.t_status='CartCompleted' OR tt.t_status='BuyNowCompleted' OR tt.t_status='CartShipped' OR tt.t_status='BuyNowShipped') AND uu.u_position='ShopOwner' AND CONVERT(DATE, t_date, 103) BETWEEN CONVERT(DATE, @fdate, 103) AND CONVERT(DATE, @tdate, 103) GROUP BY CONVERT(varchar, tt.t_date, 103) , tt.t_seller,uu.username,vs.v_id ORDER BY " + sort + " " + sc, conn);
                    cmdReport.Parameters.AddWithValue("@fdate", TextBox1.Text);
                    cmdReport.Parameters.AddWithValue("@tdate", TextBox3.Text);

                    cmdReport2 = new SqlCommand("SELECT SUM(t.t_total) FROM Transactions t,Users u WHERE u.u_id=t.t_seller AND (t.t_status='CartCompleted' OR t.t_status='BuyNowCompleted' OR t.t_status='CartShipped' OR t.t_status='BuyNowShipped') AND u.u_position='ShopOwner' AND CONVERT(DATE, t_date, 103) BETWEEN CONVERT(DATE, @fdate, 103) AND CONVERT(DATE, @tdate, 103)", conn);
                    cmdReport2.Parameters.AddWithValue("@fdate", TextBox1.Text);
                    cmdReport2.Parameters.AddWithValue("@tdate", TextBox3.Text);
                }
                else if (TextBox1.Text != "" && TextBox3.Text == "") //If From is not Empty and To is Empty
                {
                    //Obtained the Transaction ID, TOtal , Date of Transaction , Buyername , Seller Name

                    cmdReport = new SqlCommand("SELECT COUNT(tt.t_id) AS ttrans, SUM(tt.t_total) AS ttotal, CONVERT(varchar, tt.t_date, 103) AS tdate,uu.username,vs.v_id FROM Transactions tt,Users uu WHERE tt.t_seller=uu.u_id AND tt.t_seller=vs.u_id AND (tt.t_status='CartCompleted' OR tt.t_status='BuyNowCompleted' OR tt.t_status='CartShipped' OR tt.t_status='BuyNowShipped') AND uu.u_position='ShopOwner' AND CONVERT(DATE, t_date, 103) >= CONVERT(DATE, @fdate, 103) GROUP BY CONVERT(varchar, tt.t_date, 103), tt.t_seller,uu.username,vs.v_id ORDER BY " + sort + " " + sc, conn);
                    cmdReport.Parameters.AddWithValue("@fdate", TextBox1.Text);

                    cmdReport2 = new SqlCommand("SELECT SUM(t.t_total) FROM Transactions t,Users u WHERE u.u_id=t.t_seller AND (t.t_status='CartCompleted' OR t.t_status='BuyNowCompleted' OR t.t_status='CartShipped' OR t.t_status='BuyNowShipped') AND u.u_position='ShopOwner' AND CONVERT(VARCHAR, t_date, 103) >= CONVERT(DATE, @fdate, 103)", conn);
                    cmdReport2.Parameters.AddWithValue("@fdate", TextBox1.Text);
                }
                else if (TextBox1.Text == "" && TextBox3.Text != "")  //If  From is Empty and To is Not Empty
                {
                    //Obtained the Transaction ID, TOtal , Date of Transaction , Buyername , Seller Name

                    cmdReport = new SqlCommand("SELECT COUNT(tt.t_id) AS ttrans, SUM(tt.t_total) AS ttotal, CONVERT(varchar, tt.t_date, 103) AS tdate,uu.username,vs.v_id FROM Transactions tt,Users uu WHERE tt.t_seller=uu.u_id AND tt.t_seller=vs.u_id AND (tt.t_status='CartCompleted' OR tt.t_status='BuyNowCompleted' OR tt.t_status='CartShipped' OR tt.t_status='BuyNowShipped') AND uu.u_position='ShopOwner' AND CONVERT(DATE, t_date, 103) <= CONVERT(DATE, @tdate, 103) GROUP BY CONVERT(varchar, tt.t_date, 103), tt.t_seller,uu.username,vs.v_id ORDER BY " + sort + " " + sc, conn);
                    cmdReport.Parameters.AddWithValue("@tdate", TextBox3.Text);

                    cmdReport2 = new SqlCommand("SELECT SUM(t.t_total) FROM Transactions t,Users u WHERE u.u_id=t.t_seller AND (t.t_status='CartCompleted' OR t.t_status='BuyNowCompleted' OR t.t_status='CartShipped' OR t.t_status='BuyNowShipped') AND u.u_position='ShopOwner' AND (CONVERT(DATE, t_date, 103) <= CONVERT(DATE, @tdate, 103))", conn);
                    cmdReport2.Parameters.AddWithValue("@tdate", TextBox3.Text);
                }
                else // If From is Empty and To is Empty
                {
                    //Obtained the Transaction ID, Total , Date of Transaction , Buyername , Seller Name

                    cmdReport = new SqlCommand("SELECT COUNT(tt.t_id) AS ttrans, SUM(tt.t_total) AS ttotal, CONVERT(varchar, tt.t_date, 103) AS tdate, uu.username,vs.v_id FROM Transactions tt,Users uu WHERE tt.t_seller=uu.u_id AND tt.t_seller=vs.u_id AND (tt.t_status='CartCompleted' OR tt.t_status='BuyNowCompleted' OR tt.t_status='CartShipped' OR tt.t_status='BuyNowShipped') AND uu.u_position='ShopOwner' GROUP BY CONVERT(varchar, tt.t_date, 103), tt.t_seller,uu.username,vs.v_id ORDER BY " + sort + " " + sc, conn);

                    cmdReport2 = new SqlCommand("SELECT SUM(t.t_total) FROM Transactions t,Users u WHERE u.u_id=t.t_seller AND (t.t_status='CartCompleted' OR t.t_status='BuyNowCompleted' OR t.t_status='CartShipped' OR t.t_status='BuyNowShipped') AND u.u_position='ShopOwner' ", conn);
                }
                dtrReport = cmdReport.ExecuteReader();

                ItemList.DataSource = dtrReport;
                ItemList.DataBind();
                dtrReport.Close();

                double total;
                if (cmdReport2.ExecuteScalar() is DBNull)
                    total = 0;
                else
                    total = Convert.ToDouble(cmdReport2.ExecuteScalar());

                lblTotal.Text = total.ToString();
                MultiView1.ActiveViewIndex = 0;
            }
            else if (RadioButtonList1.SelectedValue == "Monthly")
            {
                if (DropDownList1.SelectedValue == "Month")
                    sort = "DATEPART(year, t_date), DATEPART(month, t_date)";
                else if (DropDownList1.SelectedValue == "Transactions")
                    sort = "ttrans";
                else if (DropDownList1.SelectedValue == "Total")
                    sort = "ttotal";


                //Obtained the year, month, counter of transaction  as ttrans, Total as ttotal

                if (ddlMonth.SelectedValue == "")
                {
                    cmdReport = new SqlCommand("SELECT DATEPART(year,t.t_date) AS tyear,DATEPART(month, t_date) AS ttmonth,  DATENAME(month,t.t_date) AS tmonth, COUNT(t.t_id) AS ttrans, SUM(t.t_total) AS ttotal,u.username FROM Users u,Transactions t WHERE u.u_id=t.t_seller AND (t.t_status='CartCompleted' OR t.t_status='BuyNowCompleted' OR t.t_status='CartShipped' OR t.t_status='BuyNowShipped') AND u.u_position='ShopOwner' AND u.u_position='ShopOwner' GROUP BY DATEPART(year, t.t_date),DATEPART(month, t.t_date), DATENAME(month,t.t_date), t.t_seller,u.username ORDER BY " + sort + " " + sc, conn);
                
                }
                else
                {
                    cmdReport = new SqlCommand("SELECT DATEPART(year,t.t_date) AS tyear,DATEPART(month, t_date) AS ttmonth,  DATENAME(month,t.t_date) AS tmonth, COUNT(t.t_id) AS ttrans, SUM(t.t_total) AS ttotal,u.username FROM Users u,Transactions t WHERE u.u_id=t.t_seller AND (t.t_status='CartCompleted' OR t.t_status='BuyNowCompleted' OR t.t_status='CartShipped' OR t.t_status='BuyNowShipped') AND u.u_position='ShopOwner' AND u.u_position='ShopOwner' GROUP BY DATEPART(year, t.t_date),DATEPART(month, t.t_date), DATENAME(month,t.t_date), t.t_seller,u.username ORDER BY " + sort + " " + sc, conn);
                    cmdReport.Parameters.AddWithValue("@tdate", ddlMonth.SelectedValue);
                }

                dtrReport = cmdReport.ExecuteReader();

                ItemList2.DataSource = dtrReport;
                ItemList2.DataBind();
                dtrReport.Close();

                cmdReport = new SqlCommand("SELECT SUM(t_total) FROM Transactions WHERE (t_status='CartCompleted' OR t_status='BuyNowCompleted' OR t_status='CartShipped' OR t_status='BuyNowShipped') AND DATEPART(year, t_date)=@tdate", conn);
                cmdReport.Parameters.AddWithValue("@tdate", ddlMonth.SelectedValue);
                double total = Convert.ToDouble(cmdReport.ExecuteScalar());
                lblTotal2.Text = total.ToString();
                MultiView1.ActiveViewIndex = 1;
            }
            else if (RadioButtonList1.SelectedValue == "Yearly")
            {
                if (DropDownList1.SelectedValue == "Year")
                    sort = "DATEPART(year, t_date)";
                else if (DropDownList1.SelectedValue == "Transactions")
                    sort = "ttrans";
                else if (DropDownList1.SelectedValue == "Total")
                    sort = "ttotal";


                //Obtained Year, Counter of Transaction ttrans, Total of Transaction t_total

                cmdReport = new SqlCommand("SELECT DATEPART(year, t.t_date) AS tyear, COUNT(t.t_id) AS ttrans, SUM(t.t_total),u.username AS ttotal FROM Transactions t, Users u WHERE u.u_id=t.t_seller AND (t.t_status='CartCompleted' OR t.t_status='BuyNowCompleted' OR t.t_status='CartShipped' OR t.t_status='BuyNowShipped') AND u.u_position='ShopOwner' GROUP BY DATEPART(year, t.t_date), t.t_seller,u.username ORDER BY " + sort + " " + sc, conn);
                dtrReport = cmdReport.ExecuteReader();

                ItemList3.DataSource = dtrReport;
                ItemList3.DataBind();
                dtrReport.Close();

                cmdReport = new SqlCommand("SELECT SUM(t_total) FROM Transactions WHERE (t_status='CartCompleted' OR t_status='BuyNowCompleted' OR t_status='CartShipped' OR t_status='BuyNowShipped')", conn);
                double total = Convert.ToDouble(cmdReport.ExecuteScalar());
                lblTotal3.Text = total.ToString();
                MultiView1.ActiveViewIndex = 2;
            }

            conn.Close();
        }

        protected void ItemList_ItemCommand(Object Sender, RepeaterCommandEventArgs e)
        {
            if (e.CommandName == "ViewShop")
            {
                int tid = Convert.ToInt32(e.CommandArgument.ToString());
                Redirect("../VirtualShop.aspx?id=" + tid);
            }
        }

        protected String formatVirtualShop(object username)
        {

            String VirtualShopName = username.ToString() + "'s Virtual Shop";


            return VirtualShopName;
        }



        private void MessageBox(string msg)
        {
            Label lbl = new Label();
            lbl.Text = "<script language='javascript'>" + Environment.NewLine + "window.alert('" + msg + "')</script>";
            Page.Controls.Add(lbl);
        }

        private void Redirect(string msg)
        {
            Label lbl = new Label();
            lbl.Text = "<script language=\"javascript\">window.open('" + msg + "', '_blank');</script>";
            Page.Controls.Add(lbl);
        }




    }
}